package com.sds.dao;

import java.sql.*;
import com.sds.util.Util_DB;
import com.sds.vo.CityVO;


public class CityDAO {

	Connection conn = null;
	PreparedStatement pstmt = null;
	ResultSet rs = null;


	public void insertCity(CityVO city) {
		try {
			conn = Util_DB.connection();
			if (conn != null) {
				pstmt = conn
						.prepareStatement("insert into t_city values(?, ?)");
				pstmt.setString(1, city.getCity_id());
				pstmt.setString(2, city.getCity_name());
				pstmt.executeUpdate();
			} else {
				System.out.println("DB Connection Error");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			Util_DB.disconnection(null, pstmt, conn);
		}
	}

	public CityVO selectCity(CityVO city) {
		CityVO cityVO = null;
		try {
			conn = Util_DB.connection();
			if (conn != null) {
				pstmt = conn
						.prepareStatement("select * from t_city where city_id = ?");
				pstmt.setString(1, city.getCity_id());
				rs = pstmt.executeQuery();
				if (rs.next()) {
					cityVO = new CityVO(rs.getString(1), rs.getString(2));
				}
			} else {
				System.out.println("DB Connection Error");
			}
			return cityVO;
		} catch (SQLException e) {
			e.printStackTrace();
			return cityVO;
		} finally {
			Util_DB.disconnection(null, pstmt, conn);
		}
	}
	/*
	public ArrayList<MemberVO> selectMemberList() {
		Connection conn = Util_DB.connection();
		PreparedStatement pstmt = null;		
=======
>>>>>>> .r67

	public ArrayList<CityVO> selectCityList() {

		ArrayList<CityVO> cityList = new ArrayList<CityVO>();
		;
		try {
			conn = Util_DB.connection();
			if (conn != null) {
				pstmt = conn.prepareStatement("select * from t_city");
				ResultSet rs = pstmt.executeQuery();

				while (rs.next()) {
					cityList.add(new CityVO(rs.getString(1), rs.getString(2)));
				}
				return cityList;
			} else {
				System.out.println("DB Connection Error");
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return cityList;
		} finally {
			Util_DB.disconnection(rs, pstmt, conn);
		}
		return cityList;
	}

	public void updateCity(CityVO city) {

		try {
			conn = Util_DB.connection();
			if (conn != null){
			pstmt = conn
					.prepareStatement("update t_city set city_name = ? where city_id = ?");

			pstmt.setString(1, city.getCity_name());
			pstmt.setString(2, city.getCity_id());

			pstmt.executeUpdate();
			}
			else {
				System.out.println("DB Connection Error");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			Util_DB.disconnection(null, pstmt, conn);
		}
	}*/

	public void deleteCity(String city_id) {

		try {
			conn = Util_DB.connection();
			if (conn != null){
			pstmt = conn.prepareStatement("delete t_city where city_id = ?");
			pstmt.setString(1, city_id);
			pstmt.executeUpdate();
			}
			else{
				System.out.println("DB Connection Error");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			Util_DB.disconnection(null, pstmt, conn);
		}
	}
}
